﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_IdentityType
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_Code
    {
        public SRM_Code()
        {
        }




        #region Helper methods
        
        public static DataTable GetSRM_Code(string codeType, string useSAPCode)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);


                cmd.CommandText = string.Format("SELECT * FROM SRM_Code WHERE CodeType = '{0}' AND IsSAPCode = {1};", codeType, useSAPCode);
                
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        public static DataTable GetSRM_Code(string codeType, string useSAPCode, string parentCode1)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);


                cmd.CommandText = string.Format("SELECT * FROM SRM_Code WHERE CodeType = '{0}' AND IsSAPCode = {1} AND ParentCodeName1 = '{2}';", codeType, useSAPCode, parentCode1);
                
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        public static DataTable GetSRM_Code(string codeType, string useSAPCode, string parentCode1, string parentCode2)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);


                cmd.CommandText = string.Format("SELECT * FROM SRM_Code WHERE CodeType = '{0}' AND IsSAPCode = {1} AND ParentCodeName1 = '{2}' AND ParentCodeName2 = '{3}';", codeType, useSAPCode, parentCode1, parentCode2);
                
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        public static DataTable GetSAP_Code(string codeType)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SAP_Code WHERE CodeTypeId = '{0}' ORDER BY DisplaySequence;", codeType);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        public static DataTable GetSAP_Code(string codeType, string parentCode1)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SAP_Code WHERE CodeTypeId = '{0}' AND ParentId1 = '{1}' ORDER BY DisplaySequence;", codeType, parentCode1);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        public static DataTable GetSAP_Code(string codeType, string parentCode1, string parentCode2)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SAP_Code WHERE CodeTypeId = '{0}' AND ParentId1 = '{1}' AND ParentId2 = '{2}' ORDER BY DisplaySequence;", codeType, parentCode1, parentCode2);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        #endregion Helper methods

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetIdentityType()
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = "SELECT * FROM SRM_Code WHERE CodeType = 'IDE' AND IsSAPCode = 1;";
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetIdentityType(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("IDE");
            else
                return GetSRM_Code("IDE", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetNationality(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("NAT");
            else
                return GetSRM_Code("NAT", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetCountry(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("CTY");
            else
                return GetSRM_Code("CTY", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetLanguage(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("LAN");
            else
                return GetSRM_Code("LAN", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetLanguageAbility(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("ABI");
            else
                return GetSRM_Code("ABI", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetLanguageProficiency(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("PRO");
            else
                return GetSRM_Code("PRO", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetSkillCategory(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("SKC");
            else
                return GetSRM_Code("SKC", useSAPCode);
        }


        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetSkillHeader(string useSAPCode)
        {
            return GetSRM_Code("SKH", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetSkillHeader(string useSAPCode, string parentCode1)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("SKH", parentCode1);
            else
                return GetSRM_Code("SKH", useSAPCode, parentCode1);
        }


        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetSkillDetail(string useSAPCode)
        {
            return GetSRM_Code("SKD", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetSkillDetail(string useSAPCode, string parentCode1, string parentCode2)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("SKD", parentCode1, parentCode2);
            else
                return GetSRM_Code("SKD", useSAPCode, parentCode1, parentCode2);
        }


        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetHobbyCategory(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("HBC");
            else
                return GetSRM_Code("HBC", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetHobbyDescription(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("HBD");
            else
                return GetSRM_Code("HBD", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetHobbyDescription(string useSAPCode, string parentCode1)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("HBD", parentCode1);
            else
                return GetSRM_Code("HBD", useSAPCode, parentCode1);
        }


        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetPurposeOfStay(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("PUR");
            else
                return GetSRM_Code("PUR", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetDurationOfStay(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("DUR");
            else
                return GetSRM_Code("DUR", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetRelationship(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("REL");
            else
                return GetSRM_Code("REL", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetResidencyType(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("RES");
            else
                return GetSRM_Code("RES", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetRace(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("RAC");
            else
                return GetSRM_Code("RAC", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetMaritalStatus(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("MAR");
            else
                return GetSRM_Code("MAR", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetDialect(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("DIA");
            else
                return GetSRM_Code("DIA", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetReligion(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("RGN");
            else
                return GetSRM_Code("RGN", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetMilitaryStatus(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("MIL");
            else
                return GetSRM_Code("MIL", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetServiceType(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("SVC");
            else
                return GetSRM_Code("SVC", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetNSStatus(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("NSS");
            else
                return GetSRM_Code("NSS", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetNSVocation(string useSAPCode, string parentCode1)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("NSV", parentCode1);
            else
                return GetSRM_Code("NSV", useSAPCode, parentCode1);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetNSRank(string useSAPCode, string parentCode1)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("RNK", parentCode1);
            else
                return GetSRM_Code("RNK", useSAPCode, parentCode1);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetIndustry(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("IND");
            else
                return GetSRM_Code("IND", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetEducationLevel(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("EDU");
            else
                return GetSRM_Code("EDU", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetEducationOrTraining(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("EDT");
            else
                return GetSRM_Code("EDT", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetQualification(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("QUA");
            else
                return GetSRM_Code("QUA", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetBranchOfStudy(string useSAPCode)
        {
            return GetSRM_Code("BOS", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetBranchOfStudy(string useSAPCode, string parentCode1)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("DIS", parentCode1);
            else
                return GetSRM_Code("DIS", useSAPCode, parentCode1);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetCourseMode(string useSAPCode)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("CMO");
            else
                return GetSRM_Code("CMO", useSAPCode);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetCertificate(string useSAPCode, string parentCode1)
        {
            if (useSAPCode == "1")
                return GetSAP_Code("CER", parentCode1);
            else
                return GetSRM_Code("CER", useSAPCode, parentCode1);
        }
    }
}